package com.jiudao.dao;

import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;

import com.google.gson.JsonArray;
import com.google.gson.JsonObject;
import com.jiudao.entity.AllAmount;
import com.jiudao.entity.Applist;
import com.jiudao.entity.JylRiskconPerson;
import com.jiudao.entity.RiskType;
import com.jiudao.entity.Riskcon;
import com.jiudao.entity.Risklist;

/**
 * 客群件均分析dao层
 * 
 * @author gyj
 *
 */
@Repository
public class CustomerTypeAverageAnalysisDao extends BaseDao {

	/**
	 * 保单总数，投保人总数，总保费
	 * 
	 * @param branch
	 * @param beginDate
	 * @param endDate
	 * @return
	 */
	public JsonObject average(String branch, String beginDate, String endDate) {
		String sql = " where 1=1 ";
		if (!StringUtils.isEmpty(branch)) {
			sql += " and " + riskcon.column(Riskcon.BRANCH) + " = '" + branch + "'";
		}
		if (!StringUtils.isEmpty(beginDate)) {
			sql += " and " + riskcon.column(Riskcon.APPDATE) + " >= '" + beginDate + "'";
		}
		if (!StringUtils.isEmpty(endDate)) {
			sql += " and " + riskcon.column(Riskcon.APPDATE) + " < '" + endDate + "'";
		}
		String sql1 = "select count(distinct " + riskcon.column(Riskcon.POLICYNO) + " ) COUNT_POLICYNO from "
				+ riskcon.tablename() + sql; // 保单总数
		String sql2 = "select count(distinct " + riskcon.column(Riskcon.APID) + " ) COUNT_PERSON from "
				+ riskcon.tablename() + sql; // 投保人总数
		String sql3 = "select sum( " + riskcon.column(Riskcon.TMOUNT) + " ) COUNT_MONEY from " + riskcon.tablename()
				+ sql; // 总保费

		logger.info(sql1);
		logger.info(sql2);
		logger.info(sql3);
		String columns1 = "COUNT_POLICYNO";
		String columns2 = "COUNT_PERSON";
		String columns3 = "COUNT_MONEY";
		double COUNT_POLICYNO = executeQuery(sql1, columns1).get(0).getAsJsonObject().get("COUNT_POLICYNO").isJsonNull() // 三目运算符判断是否为JsonNull
				? 0 : executeQuery(sql1, columns1).get(0).getAsJsonObject().get("COUNT_POLICYNO").getAsDouble();
		double COUNT_PERSON = executeQuery(sql2, columns2).get(0).getAsJsonObject().get("COUNT_PERSON").isJsonNull() ? 0
				: executeQuery(sql2, columns2).get(0).getAsJsonObject().get("COUNT_PERSON").getAsDouble();
		double COUNT_MONEY = executeQuery(sql3, columns3).get(0).getAsJsonObject().get("COUNT_MONEY").isJsonNull() ? 0
				: executeQuery(sql3, columns3).get(0).getAsJsonObject().get("COUNT_MONEY").getAsDouble();
		JsonObject jsonObject = new JsonObject();
		jsonObject.addProperty("COUNT_POLICYNO", COUNT_POLICYNO);
		jsonObject.addProperty("COUNT_PERSON", COUNT_PERSON);
		jsonObject.addProperty("COUNT_MONEY", COUNT_MONEY);
		return jsonObject;
	}

	/**
	 * 四大客群客均件数
	 * 
	 * @param branch
	 * @param beginDate
	 * @param endDate
	 * @return
	 */
	public JsonArray fourTypeAveragePolicyno(String branch, String beginDate, String endDate) {
		String sql = " select count(distinct " + riskcon.column(Riskcon.POLICYNO) + " ) COUNT_POLICYNO, "
				+ jylRiskconPerson.column(JylRiskconPerson.PERSON_MARK) + " from " + riskcon.tablename() + " left join "
				+ jylRiskconPerson.tablename() + " on " + riskcon.column(Riskcon.POLICYNO) + " = "
				+ jylRiskconPerson.column(JylRiskconPerson.POLICYNO) + " where 1=1 ";
		if (!StringUtils.isEmpty(branch)) {
			sql += " and " + riskcon.column(Riskcon.BRANCH) + " = '" + branch + "'";
		}
		if (!StringUtils.isEmpty(beginDate)) {
			sql += " and " + riskcon.column(Riskcon.APPDATE) + " >= '" + beginDate + "'";
		}
		if (!StringUtils.isEmpty(endDate)) {
			sql += " and " + riskcon.column(Riskcon.APPDATE) + " < '" + endDate + "'";
		}
		sql += " group by " + jylRiskconPerson.column(JylRiskconPerson.PERSON_MARK);

		logger.info(sql);
		String columns = "COUNT_POLICYNO," + JylRiskconPerson.PERSON_MARK;
		return executeQuery(sql, columns);
	}

	/**
	 * 四大客群客均保费
	 * 
	 * @param branch
	 * @param beginDate
	 * @param endDate
	 * @return
	 */
	public JsonArray fourTypeAverageMoney(String branch, String beginDate, String endDate) {
		String sql = " select sum( " + riskcon.column(Riskcon.TMOUNT) + " ) SUM_MONEY, "
				+ jylRiskconPerson.column(JylRiskconPerson.PERSON_MARK) + " from " + riskcon.tablename() + " left join "
				+ jylRiskconPerson.tablename() + " on " + riskcon.column(Riskcon.POLICYNO) + " = "
				+ jylRiskconPerson.column(JylRiskconPerson.POLICYNO) + " where 1=1 ";
		if (!StringUtils.isEmpty(branch)) {
			sql += " and " + riskcon.column(Riskcon.BRANCH) + " = '" + branch + "'";
		}
		if (!StringUtils.isEmpty(beginDate)) {
			sql += " and " + riskcon.column(Riskcon.APPDATE) + " >= '" + beginDate + "'";
		}
		if (!StringUtils.isEmpty(endDate)) {
			sql += " and " + riskcon.column(Riskcon.APPDATE) + " < '" + endDate + "'";
		}
		sql += " group by " + jylRiskconPerson.column(JylRiskconPerson.PERSON_MARK);

		logger.info(sql);
		String columns = "SUM_MONEY," + JylRiskconPerson.PERSON_MARK;
		return executeQuery(sql, columns);
	}

	/**
	 * 四大客群件均保额
	 * 
	 * @param branch
	 * @param beginDate
	 * @param endDat
	 * @param personMark
	 * @return
	 */
	public JsonArray fourTypeAverageMoneyForClassType(String branch, String beginDate, String endDate,
			String personMark) {
		String sql = " SELECT sum( " + jylRiskconPerson.column(JylRiskconPerson.ALL_AMOUNT)
				+ " ) SUM_AMOUNT,count(distinct " + riskcon.column(Riskcon.POLICYNO) + " ) COUNT_POLICYNO,"
				+ riskType.column(RiskType.CLASSTYPE) + " from " + riskcon.tablename() + " left join "
				+ jylRiskconPerson.tablename() + " on " + riskcon.column(Riskcon.POLICYNO) + " = "
				+ jylRiskconPerson.column(JylRiskconPerson.POLICYNO) + " left join " + risklist.tablename() + " on "
				+ riskcon.column(Riskcon.CLASSCODE) + " = " + risklist.column(Risklist.CLASSCODE) + " left join "
				+ riskType.tablename() + " on " + risklist.column(Risklist.CLASSNAME) + " = "
				+ riskType.column(RiskType.CLASSNAME) + " where 1=1 ";
		if (!StringUtils.isEmpty(branch)) {
			sql += " and " + riskcon.column(Riskcon.BRANCH) + " = '" + branch + "'";
		}
		if (!StringUtils.isEmpty(beginDate)) {
			sql += " and " + riskcon.column(Riskcon.APPDATE) + " >= '" + beginDate + "'";
		}
		if (!StringUtils.isEmpty(endDate)) {
			sql += " and " + riskcon.column(Riskcon.APPDATE) + " < '" + endDate + "'";
		}
		if (!StringUtils.isEmpty(personMark)) {
			sql += " and " + jylRiskconPerson.column(JylRiskconPerson.PERSON_MARK) + " = '" + personMark + "'";
		}
		sql += " group by " + riskType.column(RiskType.CLASSTYPE);

		logger.info(sql);
		String columns = "SUM_AMOUNT,COUNT_POLICYNO," + RiskType.CLASSTYPE;
		return executeQuery(sql, columns);
	}

}
